{
"cells": [
{
"cell_type": "markdown",
"id": "a1d74a37",
"metadata": {},
"source": [
"## Working with Excel files"
]
},
{
"cell_type": "markdown",
"id": "272e26c2",
"metadata": {},
"source": [
"- If you just want to manipulate excel's data,use pd.read_excel() and convert it to dataframe and do the manipulation\n",
"- openpyxl is the best module to work with excel's other things,like changing sheet names,formatting,automating it etc\n",
"\n",
"- indexes start at 1 not 0"
]
},
{
"cell_type": "code",
"execution_count": 26,
"id": "ec942f9b",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Requirement already satisfied: openpyxl in c:\\users\\sahil choudhary\\anaconda3\\lib\\site-packages (3.0.7)\n",
"Requirement already satisfied: et-xmlfile in c:\\users\\sahil choudhary\\anaconda3\\lib\\site-packages (from openpyxl) (1.0.1)\n"
]
}
],
"source": [
"!pip install openpyxl"
]
},
{
"cell_type": "code",
"execution_count": 27,
"id": "766191e9",
"metadata": {},
"outputs": [],
"source": [
"import openpyxl"
]
},
{
"cell_type": "code",
"execution_count": 28,
"id": "141ec445",
"metadata": {},
"outputs": [],
"source": [
"import os"
]
},
{
"cell_type": "markdown",
"id": "d2c3e254",
"metadata": {},
"source": [
"### Changing the working directory"
]
},
{
"cell_type": "code",
"execution_count": 29,
"id": "9652fa69",
"metadata": {},
"outputs": [],
"source": [
"os.chdir('C:\\\\users\\\\Sahil Choudhary\\\\Desktop')"
]
},
{
"cell_type": "markdown",
"id": "1c72de64",
"metadata": {},
"source": [
"### Open Excel file"
]
},
{
"cell_type": "code",
"execution_count": 47,
"id": "d26f9468",
"metadata": {},
"outputs": [],
"source": [
"# Use load_workbook function and it will give the workbook object\n",
"wb=openpyxl.load_workbook('example.xlsx')"
]
},
{
"cell_type": "code",
"execution_count": 48,
"id": "13646884",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"<class 'openpyxl.workbook.workbook.Workbook'>\n"
]
}
],
"source": [
"print(type(wb))"
]
},
{
"cell_type": "markdown",
"id": "6a9b4ec9",
"metadata": {},
"source": [
"### Get sheet names"
]
},
{
"cell_type": "code",
"execution_count": 49,
"id": "9f719f3b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"['Sheet1', 'Sheet2']"
]
},
"execution_count": 49,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"wb.sheetnames"
]
},
{
"cell_type": "markdown",
"id": "8027dce0",
"metadata": {},
"source": [
"### Create Sheet"
]
},
{
"cell_type": "code",
"execution_count": 63,
"id": "18f1603b",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<Worksheet \"My new Sheet\">"
]
},
"execution_count": 63,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"wb.create_sheet(title='My new Sheet',index=1)"
]
},
{
"cell_type": "markdown",
"id": "781af4b9",
"metadata": {},
"source": [
"### Get one of the Sheet"
]
},
{
"cell_type": "code",
"execution_count": 50,
"id": "14027889",
"metadata": {},
"outputs": [],
"source": [
"sheet=wb['Sheet1']"
]
},
{
"cell_type": "code",
"execution_count": 52,
"id": "82052f5c",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"<class 'openpyxl.worksheet.worksheet.Worksheet'>\n"
]
}
],
"source": [
"print(type(sheet))"
]
},
{
"cell_type": "markdown",
"id": "7823684d",
"metadata": {},
"source": [
"### Changing sheet name"
]
},
{
"cell_type": "code",
"execution_count": 51,
"id": "e427c8a1",
"metadata": {},
"outputs": [],
"source": [
"sheet.title='new name'"
]
},
{
"cell_type": "markdown",
"id": "a846dcd4",
"metadata": {},
"source": [
"### Get the number of rows and columns"
]
},
{
"cell_type": "code",
"execution_count": 59,
"id": "e5b06975",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"4\n",
"3\n"
]
}
],
"source": [
"print(sheet.max_row)\n",
"print(sheet.max_column)"
]
},
{
"cell_type": "markdown",
"id": "9e33cb31",
"metadata": {},
"source": [
"### Getting Values"
]
},
{
"cell_type": "code",
"execution_count": 53,
"id": "3966b793",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"<Cell 'new name'.B1>"
]
},
"execution_count": 53,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Getting cell object\n",
"sheet['B1']"
]
},
{
"cell_type": "code",
"execution_count": 56,
"id": "29adbe9e",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Sahil\n",
"Sahil\n"
]
}
],
"source": [
"print(sheet['B1'].value)\n",
"print(sheet.cell(row=1,column=2).value)"
]
},
{
"cell_type": "code",
"execution_count": 41,
"id": "e991e098",
"metadata": {},
"outputs": [
{
"data": {
"text/plain": [
"'=Today()+1'"
]
},
"execution_count": 41,
"metadata": {},
"output_type": "execute_result"
}
],
"source": [
"# Getting its value\n",
"sheet['A1'].value"
]
},
{
"cell_type": "markdown",
"id": "88549b4e",
"metadata": {},
"source": [
"### Changing Values"
]
},
{
"cell_type": "code",
"execution_count": 43,
"id": "22007bae",
"metadata": {},
"outputs": [],
"source": [
"sheet['B1'].value='Sahil Choudhary'\n",
"# By defauly,saves it in internal memory"
]
},
{
"cell_type": "markdown",
"id": "8ee95788",
"metadata": {},
"source": [
"### Save the result as excel file"
]
},
{
"cell_type": "code",
"execution_count": 44,
"id": "620e0a9a",
"metadata": {},
"outputs": [],
"source": [
"wb.save('example2.xlsx')"
]
},
{
"cell_type": "markdown",
"id": "c9988a20",
"metadata": {},
"source": [
"### Looping through cells"
]
},
{
"cell_type": "code",
"execution_count": 57,
"id": "601871b8",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"Sahil\n",
"Sonia\n",
"Sourav\n",
"Vishal\n"
]
}
],
"source": [
"# Get the first 5 cells of 2nd column\n",
"for i in range(1,5):\n",
" print(sheet.cell(row=i,column=2).value)"
]
},
{
"cell_type": "code",
"execution_count": 76,
"id": "b3bcbc65",
"metadata": {},
"outputs": [],
"source": [
"ws=wb['Sheet2']"
]
},
{
"cell_type": "code",
"execution_count": 77,
"id": "15fe3e3b",
"metadata": {},
"outputs": [],
"source": [
"rows=ws.iter_rows(min_row=1,max_row=7,min_col=1,max_col=2)\n",
"# we have iter_cols as well\n",
"# returns generator object"
]
},
{
"cell_type": "code",
"execution_count": 78,
"id": "30c13f9b",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"(<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.B1>)\n",
"(<Cell 'Sheet2'.A2>, <Cell 'Sheet2'.B2>)\n",
"(<Cell 'Sheet2'.A3>, <Cell 'Sheet2'.B3>)\n",
"(<Cell 'Sheet2'.A4>, <Cell 'Sheet2'.B4>)\n",
"(<Cell 'Sheet2'.A5>, <Cell 'Sheet2'.B5>)\n",
"(<Cell 'Sheet2'.A6>, <Cell 'Sheet2'.B6>)\n",
"(<Cell 'Sheet2'.A7>, <Cell 'Sheet2'.B7>)\n"
]
}
],
"source": [
"for row in rows:\n",
" print(row)\n",
" # returns tupple of cell locations"
]
},
{
"cell_type": "code",
"execution_count": 79,
"id": "da1db7ef",
"metadata": {},
"outputs": [],
"source": [
"for a,b in rows:\n",
" print(a.value,b.value)\n",
" # returns tupple of cell locations"
]
},
{
"cell_type": "markdown",
"id": "ba45f4c5",
"metadata": {},
"source": [
"### Get column name by number"
]
},
{
"cell_type": "code",
"execution_count": 61,
"id": "2bf67343",
"metadata": {},
"outputs": [
{
"name": "stdout",
"output_type": "stream",
"text": [
"A\n"
]
}
],
"source": [
"print(openpyxl.utils.cell.get_column_letter(1))"
]
},
{
"cell_type": "markdown",
"id": "c1221862",
"metadata": {},
"source": [
"### Change format of cells"
]
},
{
"cell_type": "code",
"execution_count": 66,
"id": "560132bb",
"metadata": {},
"outputs": [],
"source": [
"# create font object and give it to cell\n",
"from openpyxl.styles import Font\n",
"sheet['B1'].font=Font(sz=14,bold=True,italic=True)\n",
"\n",
"# save it as new file in end"
]
},
{
"cell_type": "markdown",
"id": "8904fb2b",
"metadata": {},
"source": [
"### Adding Border"
]
},
{
"cell_type": "code",
"execution_count": 68,
"id": "5ddc3d6a",
"metadata": {},
"outputs": [],
"source": [
"from openpyxl.styles import Border,Side\n",
"\n",
"# Pass color and border style to Side\n",
"# top=Side(border_style='thin|thick|medium|dashed|double',color='hexcode')\n",
"top=Side(border_style='thin')\n",
"\n",
"# Border(top=top,bottom=bottom,left=left,right=right)\n",
"border=Border(top=top)\n",
"\n",
"sheet['B1'].border=border"
]
}
],
"metadata": {
"kernelspec": {
"display_name": "Python 3",
"language": "python",
"name": "python3"
},
"language_info": {
"codemirror_mode": {
"name": "ipython",
"version": 3
},
"file_extension": ".py",
"mimetype": "text/x-python",
"name": "python",
"nbconvert_exporter": "python",
"pygments_lexer": "ipython3",
"version": "3.8.8"
}
},
"nbformat": 4,
"nbformat_minor": 5
}